﻿package com.rocky.project.tools;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.InetAddress;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.rocky.project.model.base.BaseEntity;
import com.rocky.project.model.family.Archive;
import com.rocky.project.model.family.Family;
import com.rocky.project.model.family.Person;
import com.rocky.project.service.BaseService;

/**
 * 
 */

/**
 * 家庭导入工具
 * 
 * @author chen
 * @since 1.2
 * @version 1.0
 */
public class PersonImporterImpl extends BaseDirectoryImporter {

	/**
	 * 成功数量
	 */
	private int rowSuccessCount = 0;

	/**
	 * 错误数量
	 */
	private int rowErrorCount = 0;

	/**
	 * 错误列表
	 */
	private ArrayList<String> rowErrors = new ArrayList<String>();

	protected BaseService baseService;

	protected SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");

	private Date now = new Date();

	private String ip = "192.168.0.1";

	public PersonImporterImpl() {
		// 初始化时候取本机IP
		try {
			ip = InetAddress.getLocalHost().getHostAddress();
		} catch (Exception ex) {
			log.debug("取本机IP发生错误");
			log.debug(ex);
			ip = "127.0.0.1";
		}
	}

	public BaseService getBaseService() {
		return baseService;
	}

	public void setBaseService(BaseService baseService) {
		this.baseService = baseService;
	}

	@Override
	public void beginImport() throws IOException {
		log.info("##excel导入程序开始执行！");
		super.beginImport();
		log.info("##excel导入程序执行完毕！");
		log.info("导入结果，成功：" + this.successCount + "# 失败" + this.errorCount + "个：");
		
		for (String tmp : this.errors) {
			log.info(tmp);
		}

	}

	@Override
	protected void parseFile(File file) throws IOException {
		String canonicalPath = file.getAbsolutePath();
		String[] temp = canonicalPath.split("\\.");
		String type = temp[temp.length - 1];
		if (type.equals("xls")) {
			log.info("解析excel文件:" + canonicalPath);
			this.rowSuccessCount = this.rowErrorCount = 0;
			this.rowErrors.clear();
			InputStream myxls = new FileInputStream(file);
			try {
				HSSFWorkbook wb = new HSSFWorkbook(myxls);
				HSSFSheet sheet = wb.getSheetAt(0);
				for (int i = 2; i > 0; i++) {// 怎么判断没有行可读了，没有列可读了
					try {
						parseRow(sheet.getRow(i));
						this.rowSuccessCount++;
					} catch (Exception ex) {
						log.info(canonicalPath + "数据行(" + (i + 1) + ")数据不完整，中止导入，请检查是否是结束行！");
						this.rowErrorCount++;
						this.rowErrors.add(canonicalPath + "数据行(" + (i + 1) + ")数据不完整，中止导入，请检查是否是结束行");
						break;
					}
				}
				successCount++;
			} catch (Exception e) {
				log.info("excel格式异常，请仔细检查！");
				errorCount++;
				errors.add("excel导入异常：" + canonicalPath);
			} finally {
				myxls.close();
				log.info("完成excel数据导入：" + canonicalPath);
				log.info("成功：" + this.rowSuccessCount);
				log.info("失败：" + this.rowErrorCount);
				if (this.rowErrorCount > 1) {//默认有一个错误读到空行！
					log.info("失败如下：");
					for (String tmp : this.rowErrors) {
						log.info(tmp);
					}
				}
			}
		} else {
			log.info("忽略非excel文件：" + file.getCanonicalPath());
		}
	}

	/**
	 * @param row
	 *            解析一个单元格为一个Person对象,判断当户主名称改变的时候进行入库flush arraylist中的内容。
	 */

	protected boolean parseRow(HSSFRow row) throws Exception {
		HSSFCell cell = null; // 第n个单元格
		// cell=row.getCell((short)0);//序号		
		cell = row.getCell((short) 1);// 姓名
		Object nameObj = null;
		try {
			nameObj = readFromCell(cell);
		} catch (Exception e) {
			return false;
		}
		String name = (nameObj == null) ? "" : nameObj.toString().trim();
		cell = row.getCell((short) 2);// 性别
		String sex = readFromCell(cell).toString().trim() == "男" ? "1" : "2";
		Date birthday = null;
		cell = row.getCell((short) 3);// 出生年月		
		try {
			birthday = df.parse(this.readWithoutEx(cell));
		} catch (Exception e) {
			log.debug("###person.setBirthday(df.parse(birthday));##");
		}
		cell = row.getCell((short) 4);// 身份证号码
		String cardnumber = readWithoutEx(cell);
		if (cardnumber.length() < 6) {
			cardnumber = "录入格式不正确";
		}
		cell = row.getCell((short) 5);// 户主姓名
		String houserholderName = readFromCell(cell).toString().trim();
		cell = row.getCell((short) 6);// 户主关系
		String relation = readFromCell(cell).toString().trim();
		cell = row.getCell((short) 7);// 家庭地址
		String address = readFromCell(cell).toString().trim();
		cell = row.getCell((short) 8);// 用地面积
		String foundationArea = this.readWithoutEx(cell);
		cell = row.getCell((short) 9);// 建筑面积
		String constructionArea = this.readWithoutEx(cell);
		cell = row.getCell((short) 10);// 档案备注
		String description = this.readWithoutEx(cell);
		Person person = null;
		Archive archive = null;
		Family family = null;

		try {
			if ("本人".equals(relation) || "户主".equals(relation)) {//户主
				Collection ps = baseService.executeHqlWithParam("select m from Person as m where m.name=? and address=? ", new Object[] { name, address }, 0, null);
				if (ps != null && !ps.isEmpty()) {//户主&&已存在 更新这些空壳
					person = (Person) ps.iterator().next();
					person.setSex(sex);
					person.setBirthday(birthday);
					person.setCardnumber(cardnumber);
					person.setHouseholderRelation("本人");
					family = person.getFamily();
					archive = (Archive) baseService.executeHqlWithParam("select m from Archive as m where m.family.id=?  ", new Object[] { family.getId() }, 0, null).iterator().next();
					// 用地面积
					archive.setFoundationArea(foundationArea);
					// 建筑面积
					archive.setConstructionArea(constructionArea);
					// 档案备注
					archive.setDescription(description);
					baseService.update(person);
					//				baseService.update(family);
					baseService.update(archive);
					log.debug("户主&已存在：" + person.getName());
				} else {//户主&&不存在
					initFamily(name, sex, birthday, cardnumber, houserholderName, address, foundationArea, constructionArea, description);
				}
			} else {//非户主
				//数据合理性判断
				if (name.equals(houserholderName)) {
					this.errorCount++;
					this.errors.add("户主关系非‘本人’，但是户主姓名和姓名相同:" + name);
					return false;
				}
				if (baseService.getCountHqlWithParam("select m from Person as m where m.name=? and address=? ", new Object[] { name, address }) > 0) {
					this.errorCount++;
					this.errors.add("户主关系非‘本人’，但是该农民信息已存在:" + name);
					return false;
				}
				person = new Person();
				initBaseEntity(person);
				person.setName(name);
				person.setSex(sex);
				person.setBirthday(birthday);
				person.setCardnumber(cardnumber);
				person.setHouseholder(houserholderName);
				person.setAddress(address);
				person.setHouseholderRelation(relation);
				person.setType("Person");
				//是否存在本人，不重复录入log.debug("非户主但是已存在：" + person.getName());
				//数据合理性判断
				Collection ps = baseService.executeHqlWithParam("select m from Person as m where m.name=? and address=? ", new Object[] { houserholderName, address }, 0, null);
				if (ps != null && !ps.isEmpty()) {//非户主&&已存在户主
					Person personHouseholder = (Person) ps.iterator().next();
					family = personHouseholder.getFamily();
					log.debug("非户主&找到户主：" + personHouseholder.getName());
				} else {//非户主&&不存在户主
					family = initFamily(houserholderName, "", null, "", houserholderName, address, foundationArea, constructionArea, description);
					log.debug("非户主&&不存在户主：" + family.getHouseholder().getName());
				}
				person.setFamily(family);
				baseService.create(person);
			}
		} catch (Exception e) {
			this.errorCount++;
			this.errors.add("个人信息导入到数据库时发生错误，姓名为：：" + name + "");

		}
		return true;
	}

	private Family initFamily(String name, String sex, Date birthday, String cardnumber, String householder, String address, String foundationArea, String constructionArea, String description) {

		Person personHouseholder = new Person();
		Archive archive = new Archive();
		Family family = new Family();

		personHouseholder.setName(name);
		personHouseholder.setSex(sex);
		personHouseholder.setBirthday(birthday);
		personHouseholder.setCardnumber(cardnumber);
		personHouseholder.setHouseholder(householder);
		personHouseholder.setAddress(address);
		personHouseholder.setHouseholderRelation("本人");
		personHouseholder.setType("Person");

		family.setHouseholdername(householder);
		family.setAddress(address);
		family.setType("Family");

		// 用地面积
		archive.setFoundationArea(foundationArea);
		// 建筑面积
		archive.setConstructionArea(constructionArea);
		// 档案备注
		archive.setDescription(description);

		initBaseEntity(personHouseholder);
		initBaseEntity(family);
		initBaseEntity(archive);

		baseService.create(personHouseholder);
		baseService.create(family);
		baseService.create(archive);

		personHouseholder.setFamily(family);
		family.setHouseholder(personHouseholder);
		archive.setFamily(family);

		baseService.update(personHouseholder);
		baseService.update(family);
		baseService.update(archive);
		return family;
	}

	/**
	 * 没有异常的读取方法，读取可允许为空的字段
	 * 
	 * @param cell
	 *            单元格
	 * @return 对象字符串
	 */
	protected String readWithoutEx(HSSFCell cell) {
		try {
			return readFromCell(cell).toString().trim();
		} catch (Exception ex) {
			return "";
		}
	}

	/**
	 * 从Excel表格单元读取一个对象出来,如果对象 不是字符串和日期或者数字类型，仅仅读取对象的类别 不支持图像之类的读写
	 * 
	 * @param cell
	 *            单元格
	 * @return 读取的结果对象
	 */
	protected Object readFromCell(HSSFCell cell) {
		Object result = null;
		if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
			result = cell.getStringCellValue();
			log.info("单元格是字符串，值是： " + result);
		} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				result = cell.getDateCellValue();
				log.info("单元格是日期，值是：" + result);
			} else {
				result = cell.getNumericCellValue();
				log.info("单元格是数字，值是： " + result);
			}
		} else {
			result = cell.getCellType();
			log.info("单元格的值不是字符串或数值。类型是：" + result);
			result="";
		}
		return result;

	}

	/**
	 * @param user
	 *            初始化一个User或者Family对象
	 */
	private void initBaseEntity(BaseEntity user) {
		user.setCreateIp(ip);
		user.setCreateTime(now);
		user.setCreator("importer");
	}

}